package com.student.service;

import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.student.DBConn;
import com.student.entities.CartEntity;
import com.student.entities.OrderEntity;
import com.student.entities.OrderItemEntity;
import com.student.util.OrderNo;
import com.student.util.Util;

public class OrderService {
	// 保存订单信息
	public static String saveOrder(String orderString, int userId,
			String toAddr, String toUser, String toTime) throws SQLException {
		// 生成订单号
		String orderNo = "";
		try {
			orderNo = OrderNo.getOdn();
			System.out.println("订单号" + orderNo);
		} catch (Exception e) {
			e.printStackTrace();
			return "ERROR";
		}

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		try {
			// 更改JDBC事务的默认提交方式
			conn.setAutoCommit(false);
			// 将Json数据转化为订单项列表
			List<OrderItemEntity> orderList = new ArrayList<OrderItemEntity>();
			Type listType = new TypeToken<ArrayList<OrderItemEntity>>() {
			}.getType();
			Gson gson = new Gson();
			orderList = gson.fromJson(orderString, listType);
			// 将订单项插入数据库，（orderItem）
			// 并按商家进行分组处理,为插入订单表（order）做准备
			Map<Integer, List<OrderItemEntity>> map = new HashMap<Integer, List<OrderItemEntity>>();
			for (OrderItemEntity order : orderList) {
				// 将订单数据插入订单表
				stmt.execute(toItemSQL(order, userId, orderNo));
				// 订单分组
				// 已有当前订单项应该分配到的组
				if (map.containsKey(order.getSellerId())) {
					map.get(order.getSellerId()).add(order);
				} else {
					List<OrderItemEntity> tempList = new ArrayList<OrderItemEntity>();
					tempList.add(order);
					map.put(order.getSellerId(), tempList);
				}
			}
			System.out.println("---->" + map);
			// 利用keyset对Map进行遍历，它的优点在于可以根据你所想要的key值得到你想要的 values，更具灵活性！！
			Set<Integer> keySet = map.keySet();
			for (Iterator it = keySet.iterator(); it.hasNext();) {
				Integer key = (Integer) it.next();
				List<OrderItemEntity> temp = map.get(key);
				String orderSql = toOrderString(key, userId, temp, orderNo,
						toAddr, toUser, toTime);
				System.out.println(orderSql + "=======");
				stmt.execute(orderSql);
			}
			// 提交JDBC事务
			conn.commit();
			// 恢复JDBC事务的默认提交方式
			conn.setAutoCommit(true);
			stmt.close();
			conn.close();
			// 操作成功，返回订单数据
			return orderNo;
		} catch (Exception ex) {
			// 回滚JDBC事务
			conn.rollback();
			ex.printStackTrace();
			stmt.close();
			conn.close();
			// 数据库操作失败，返回ERROR
			return "ERROR";
		}
		// }
	}

	// 根据订单项数据生成订单插入语句
	private static String toOrderString(Integer sellerId, int userId,
			List<OrderItemEntity> orderList, String orderNo, String toAddr,
			String toUser, String toTime) throws SQLException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = sdf.format(new java.util.Date());
		String sql = "insert into shop_order values( null ,'" + userId + "','"
				+ orderNo + "','" + sellerId + "','" + toOrder(orderList)
				+ "','" + toAddr + "','" + time + "','" + toUser + "','"
				+ toTime + "','1',0,null);";
		System.out.println(sql + "---->");
		return sql;
	}

	// 根据订单项列表生成订单描述
	private static String toOrder(List<OrderItemEntity> orderList)
			throws SQLException {
		String orderInfo = "";
		for (OrderItemEntity order : orderList) {
			orderInfo += GoodsService.getPointGoods(order.getGoodsId())
					.getName() + "[" + order.getCount() + " 份],";
		}
		return orderInfo.substring(0, orderInfo.length() - 1);
	}

	// 根据订单项列表生成订单项插入语句
	private static String toItemSQL(OrderItemEntity order, int userId,
			String orderNo) {
		String sql = "insert into shop_orderitem  values( null ,'"
				+ order.getSellerId() + "','" + order.getGoodsId() + "','"
				+ userId + "','" + order.getCount() + "','" + order.getIspaid()
				+ "','" + order.getAddTime() + "','" + orderNo + "','"
				+ order.getIsComment() + "','1');";
		return sql;
	}

	// 查询订单数据(针对用户)
	// 当用户需要从服务器端查询自己的 购物历史时
	public static List<OrderEntity> getUserOrder(int userId, String start,
			String count) throws SQLException {
		List<OrderEntity> orderList = new ArrayList<OrderEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();

		String sql = "select * from shop_order where  order_userId = " + userId
				+ "  group by order_no  order by order_addtime desc limit "
				+ start + "," + count;

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			OrderEntity order = new OrderEntity();
			order.setId(rs.getInt("order_id"));
			order.setDetail(rs.getString("order_detail"));
			order.setNo(rs.getString("order_no"));
			order.setSellerId(rs.getInt("order_sellerId"));
			order.setTime(sdf.format(rs.getDate("order_addTime")));
			order.setToAddr(rs.getString("order_state"));
			order.setToAddr(rs.getString("order_deliveryAddr"));
			order.setToUser(rs.getString("order_deliveryUser"));
			order.setToTime(rs.getString("order_deliveryTime"));
			order.setState(rs.getInt("order_state"));
			order.setCartList(getUserCart(userId, rs.getString("order_no")));
			orderList.add(order);
		}
		rs.close();
		stmt.close();
		conn.close();
		return orderList;

	}

	// 获得订单项数据
	private static List<CartEntity> getUserCart(int userId, String orderNo)
			throws SQLException {
		List<CartEntity> cartList = new ArrayList<CartEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();

		String sql = " select  *  from shop_orderitem , shop_goods , shop_seller "
				+ " where item_goodsId = goods_id and goods_sellerId = seller_id "
				+ " and item_orderNo like                                       '"
				+ orderNo + "' and item_userId = " + userId;

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			CartEntity cart = new CartEntity();
			cart.setCart_count(rs.getInt("item_count"));
			cart.setCart_id(rs.getInt("item_id"));
			cart.setCart_name(rs.getString("goods_name"));
			cart.setCart_no(rs.getString("item_orderNo"));
			cart.setCart_price(rs.getDouble("goods_price"));
			cart.setCart_sellerName(rs.getString("seller_name"));
			cart.setCart_state(rs.getInt("item_state"));
			cart.setCart_time(sdf.format(rs.getTimestamp("item_addtime")));

			cartList.add(cart);
		}
		rs.close();
		stmt.close();
		conn.close();
		return cartList;
	}

	// 订单统计
	public static String orderCount(int userId) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String result = "";
		// 总共定了几次外卖
		int count = 0;
		String countSql = "select count(*) from shop_user, shop_order where shop_user.user_id = shop_order.order_userId and shop_user.user_id = "
				+ userId + " group by shop_order.order_no";

		ResultSet rs = stmt.executeQuery(countSql);
		while (rs.next()) {
			count++;
		}
		result += count + ",";

		// 平均花费
		String priceSql = "select goods_price ,item_count  from  shop_goods , shop_orderitem where shop_orderitem.item_goodsId = shop_goods.goods_id and "
				+ "item_userId = " + userId;
		rs = stmt.executeQuery(priceSql);
		double priceAll = 0.0;
		while (rs.next()) {
			priceAll += rs.getDouble(1) * rs.getInt(2);
		}
		result += String.format("%.1f", priceAll / count) + ",";

		// 最喜欢的商家及去的次数
		String hotSeller = "select seller_name, count(*) as count from shop_order,shop_seller where seller_id = order_sellerId and order_userId = "
				+ userId + " group by order_sellerId ";
		rs = stmt.executeQuery(hotSeller);
		if(rs.next()){
			result += rs.getString(1) + "," + rs.getInt(2) + ",";
		}else{
			result += "无记录,0,";
		}
		// 最喜欢的菜肴及购买次数
		String hotGoods = "select goods_name, count(*) as count  from shop_goods , shop_orderitem where goods_id = item_goodsId and item_userId = "
				+ userId + " group by item_goodsId order by count desc";
		rs = stmt.executeQuery(hotGoods);
		if(rs.next()){
			result += rs.getString(1) + "," + rs.getInt(2);
		}else{
			result += "无记录,0,";
		}
		rs.close();
		stmt.close();
		conn.close();
		return result;
	}

	public static void main(String[] args) throws SQLException {
		// List<OrderEntity> orderList = getUserOrder(3, 0 + "", 30 + "");
		// for (OrderEntity order : orderList) {
		// System.out.println("订单是" + order);
		// // System.out.println("详情是" + order.getOrderItemList());
		// }

		System.out.println(orderCount(10));
	}
}